Database applications having poor performance may result from many causes. One of the more common causes results from issues with query execution. In many databases, queries consist of Structured Query Language (SQL) queries, but the problems may result in other query languages and structures.
Locating a problem query statement or statement amongst the large numbers of well-performing queries in a runtime database environment is a daunting and laborious task. This has led to people working in development to attempt to locate problematic query statements during the design and development process. People working in all phases of application development, including developers, quality assurance engineers, database administrators, and performance engineers, spend considerable efforts to detect and correct problematic queries.
This process typically involves the person monitoring the database during application execution for queries and then removing the well-performing queries. This leaves the potentially problematic queries. One must note that the definition of ‘problematic’ may be subjective or may depend upon different criteria (execution time, response time, resource usages, etc.) for different databases. As used here, a ‘problematic’ query means one that causes the database to execute in a manner that is below expectations in at least one aspect (time, resource, results, etc.).
Once only problematic query statements remain in the database, the person must manually gather the entire database provided information relating to the suspect query statement. At this point, the determination must be made as to whether the statement is really problematic. Again, this determination may be subjective. If the query statement is determined to be problematic, the person would then make a correction (if a developer) or provide suggestions (if someone other than a developer) as to how to correct the statement to increase performance.
Typically, this process has to be repeated for every database, and coordination between monitoring efforts is lacking, so problematic statements running in a first database may occur in a second database because there was no way to know that the statement had problems in the first database. As the physical number of monitored databases grows, duplication of effort and the chance of conflicting corrections grow significantly.